package com.huanf.controller;

import com.huanf.domain.MySQLToExcelVo;
import com.huanf.service.MySQLToExcelService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * @author: 35238
 * 功能:
 * 时间: 2023-12-24 23:06
 */
@RestController
@Slf4j
public class MySQLToExcelController {

    @Autowired
    private MySQLToExcelService MySQLToExcelService;

    @GetMapping("/toExcel") //浏览器访问 http://localhost:8080/toExcel
    public void demo() throws IOException {

        //文件保存到哪里，还要指定文件名
        String PATH = "E:\\桌面\\Java-Ready\\conf\\Excel\\MySQLToExcel.xlsx";

        //从数据库查询数据
        List<MySQLToExcelVo> productList = MySQLToExcelService.list();

        //调用下面写好的方法
        xxwrite(productList, PATH);
    }

    /**
     * 将数据库的数据写入Excel表格
     */
    public void xxwrite(List<MySQLToExcelVo> productList, String path) throws IOException{
        //创建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("MySQLToExcel数据");

        //第一行的数据，也就是每列的列头
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("姓名");
        row.createCell(1).setCellValue("性别");
        row.createCell(2).setCellValue("年龄");
        row.createCell(3).setCellValue("手机号码");
        row.createCell(4).setCellValue("邮箱");
        row.createCell(5).setCellValue("加入时间");
        row.createCell(6).setCellValue("地址");
        row.createCell(7).setCellValue("状态");

        //其它行的数据
        for (int i = 0; i < productList.size(); i++) {
            XSSFRow row1 = sheet.createRow(i + 1);
            row1.createCell(0).setCellValue(productList.get(i).getName());
            row1.createCell(1).setCellValue(productList.get(i).getSex());
            row1.createCell(2).setCellValue(productList.get(i).getAge());
            row1.createCell(3).setCellValue(productList.get(i).getPhone());
            row1.createCell(4).setCellValue(productList.get(i).getEmail());
            row1.createCell(5).setCellValue(productList.get(i).getEntryTime());
            row1.createCell(6).setCellValue(productList.get(i).getAddress());
            row1.createCell(7).setCellValue(productList.get(i).getStatus());
        }

        FileOutputStream fileOutputStream = new FileOutputStream(path);
        try {
            xssfWorkbook.write(fileOutputStream);
        } catch (IOException e) {
            log.error("写入Excel失败: {}",e.getMessage());;
        } finally {
            fileOutputStream.flush();
            fileOutputStream.close();
        }
    }
}
